package ict.kth.se.model.repository.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.GrantedAuthorityImpl;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;

import ict.kth.se.exceptions.EntityAlreadyExistsInDBException;
import ict.kth.se.exceptions.EntityDoesntExistInDBException;
import ict.kth.se.model.Person;
import ict.kth.se.model.Role;
import ict.kth.se.model.repository.PersonRepository;
import ict.kth.se.model.repository.RoleRepository;

/**
 * This class handles communication with database for Person object. It implements PersonRepository to implement all base methods
 * for persons. It also implements UserDetailsService from Springsource to be able to retrieve users that should log in.
 */
public class JdbcPersonRepository implements UserDetailsService, PersonRepository {

	private JdbcTemplate jdbcTemplate;
	private RowMapper<Person> rowMapper = new PersonRowMapper();
	private RoleRepository roleRepository;
	
	/**
	 * public constructor
	 * @param dataSource datasource to set jdbctemplate
	 * @param roleRepository rolerepository to be set
	 */
	public JdbcPersonRepository(DataSource dataSource, RoleRepository roleRepository) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.roleRepository = roleRepository;
	}
	
	/**
	 * Method for getting all persons
	 * @return All persons
	 */
	@Override
	public List<Person> getAllPersons() throws SQLException{
		try{
			String sql = "SELECT * FROM Person;";
			return jdbcTemplate.query(sql, rowMapper);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method retrieves person by it's id
	 * @param id Id of person which is to be found
	 * @return Found person or null if none found
	 * @throws SQLException 
	 */
	@Override
	public Person getPersonById(Long id) throws SQLException{
		try{
			String sql = "SELECT * FROM Person WHERE person_id = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, id);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method retrieves person by it's ssn
	 * @param ssn Ssn of person which is to be found
	 * @return Found person or null if none found
	 * @throws SQLException 
	 */
	@Override
	public Person getPersonBySsn(String ssn) throws SQLException{
		try{
			String sql = "SELECT * FROM Person WHERE ssn = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, ssn);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method removes one user from database (by it's ID)
	 * @param person The person to be removed
	 * @throws EntityDoesntExistInDBException If person doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removePerson(Person person) throws EntityDoesntExistInDBException, SQLException{
		
		if(this.getPersonById(person.getId()) == null)
			throw new EntityDoesntExistInDBException("No such person in database");
		
		String sql = "DELETE FROM Person WHERE person_id = ?;";
		jdbcTemplate.update(sql, person.getId());
	}
	
	/**
	 * This method adds an person in DB
	 * @param person Person to be added
	 * @return Newly created person
	 * @throws EntityAlreadyExistsInDBException If such person already exists in DB (by username)
	 * @throws SQLException
	 */
	@Override
	public Person createPerson(Person person) throws EntityAlreadyExistsInDBException, SQLException{
		
		//Some controll before insertion - if user is enabled to log in and there is already such username
		if(person.isEnabled() && this.loadUserByUsername(person.getUsername()) != null){
			throw new EntityAlreadyExistsInDBException("There is already such enabled user in DB.");
		}
		
		//If not than add it
		KeyHolder keyHolder = new GeneratedKeyHolder();
		//Now we have to define all variables as final to be used in prepared statement
		final String firstName = person.getFirstName();
		final String lastName = person.getLastName();
		final String email = person.getEmail();
		final String ssn = person.getSsn();
		final String username = person.getUsername();
		final String password = person.getPassword();
		final Long roleId = person.getRole().getId();
		final boolean enabled = person.isEnabled();
		
		final String sql = "INSERT INTO Person (name, surname, ssn, email, username, password, role_id, user_enabled) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
		
		jdbcTemplate.update(
			    new PreparedStatementCreator() {
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"person_id"});
			            ps.setString(1, firstName);
			            ps.setString(2, lastName);
			            ps.setString(3, ssn);
			            ps.setString(4, email);
			            ps.setString(5, username);
			            ps.setString(6, password);
			            ps.setLong(7, roleId);
			            ps.setBoolean(8, enabled);
			            return ps;
			        }
			    },
			    keyHolder);
		
		//After update add new id and return
		person.setId(keyHolder.getKey().longValue());
		return person;
	}
	
	/**
	 * This method updates an person in DB
	 * @param person Person to be updated
	 * @throws EntityDoesntExistInDBException If such person doesn't exist in DB (by id)
	 * @throws SQLException
	 */
	@Override
	public void updatePerson(Person person) throws EntityDoesntExistInDBException, SQLException{
		
		//Some controll before updating
		if(this.getPersonById(person.getId()) == null)
			throw new EntityDoesntExistInDBException("No such person in database");
		
		if(person.getFirstName() == null || person.getLastName() == null)
			throw new SQLException("Corrupted person's name.");
		
		if(person.getRole() == null || this.roleRepository.getRoleById(person.getRole().getId()) == null)
			throw new SQLException("Corrupted role on person.");
		
		String sql = "UPDATE Person SET name = ?, surname = ?, ssn = ?, email = ?, username = ?, " +
				"password = ?, role_id = ?, user_enabled = ? WHERE person_id = ?;";
		
		jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(), person.getSsn(), person.getEmail(),
				person.getUsername(), person.getPassword(), person.getRole().getId(), person.isEnabled(), person.getId());
	}
	
	/**
	 * This method overrides method from UserDetailsService to get user that should log in from DB 
	 * @param username Username of the user to be retrieved
	 * @return Found UserDetails
	 * @throws UsernameNotFoundException If username isn't found in DB
	 * @throws DataAccessException
	 */
	public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException, DataAccessException {
		try{
			String sql = "SELECT * FROM Person WHERE username = ?";
			return jdbcTemplate.queryForObject(sql, rowMapper, username);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/*
	 * RowMapper to map persons objects
	 */
	class PersonRowMapper implements RowMapper<Person>{
		public Person mapRow(ResultSet rs, int rowNum) throws SQLException{
			return mapPerson(rs);
		}
	}
	
	/**
	 * Maps a row returned from a query of Person to a Person object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	private Person mapPerson(ResultSet rs) throws SQLException {
		
		//Get authorities
		List<GrantedAuthority> authList = new ArrayList<GrantedAuthority>();
		Role role = roleRepository.getRoleById(rs.getLong("role_id"));
		authList.add(new GrantedAuthorityImpl(role.getName()));
		
		//Constructor with base params
		Person person = new Person(rs.getLong("person_id"),
				rs.getString("name"),
				rs.getString("surname"),
				rs.getString("ssn"),
				rs.getString("email"),
				rs.getString("username"),
				rs.getString("password"),
				rs.getBoolean("user_enabled"),
				authList
				);
		person.setRole(role);
		
		return person;
	}
}
